package com.test.wog.dao;

import com.test.wog.bean.RegularBus;
import com.test.wog.util.GeometryTrimUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @author LX
 * @since 2020-01-27
 */
@Repository
public class RegularBusDAOImpl implements RegularBusDAO {

    @Autowired
    JdbcTemplate jdbc;
    @Autowired
    GeometryTrimUtil gtu;
    @Override
    public List<RegularBus> getRegularBusByOriDestType(String ori, String dest, String type) {
        String sql = "select id, origin, destination, type, length, duration, " + gtu.trimLine("ST_ASTEXT(geom)") + " as geom from regular_bus_route where origin = ? and destination = ? and type like ?";
        List<RegularBus> regularBusList = jdbc.query(
                sql,
                new Object[]{ori, dest, '%' + type + '%'},
                new BeanPropertyRowMapper(RegularBus.class)
        );
        return regularBusList;
    }

    @Override
    public List<RegularBus> getRegularBusByDestType(int dest, String type) {
        String sql = "select id, origin, destination, type, length, duration, " + gtu.trimLine("ST_ASTEXT(geom)") + " as geom from regular_bus_route where destination = ? and type like ?";
        List<RegularBus> regularBusList = jdbc.query(
                sql,
                new Object[]{dest, '%' + type + '%'},
                new BeanPropertyRowMapper(RegularBus.class)
        );
        return regularBusList;
    }

    @Override
    public List<RegularBus> getRegularBusByIdAndType(int id, String type) {
        String sql = "select id, origin, destination, type, length, duration, " + gtu.trimLine("ST_ASTEXT(geom)") + " as geom from regular_bus_route where id = ? and type like ?";
        List<RegularBus> regularBusList = jdbc.query(
                sql,
                new Object[]{id, '%' + type + '%'},
                new BeanPropertyRowMapper(RegularBus.class)
        );
        return regularBusList;
    }

    @Override
    public List<RegularBus> getRegularBusByIdAndTime(int id, String startTime) {//寻找编号为id，发车时间小于等于startTime的班车车次
        String sql = "select start_time from regular_bus_time where bus_id = ? and start_time < ? order by start_time desc";
        List<RegularBus> regularBusList = jdbc.query(
                sql,
                new Object[]{id, startTime},
                new BeanPropertyRowMapper(RegularBus.class)
        );
        return regularBusList;
    }

    @Override
    public List<RegularBus> getRegularBusByType(String type) {
        String sql = "select t1.id, origin, destination, " + gtu.trimLine("ST_ASTEXT(geom)") + " as geom, length, duration, start_time from regular_bus_route t1  LEFT JOIN regular_bus_time t2 on t1.id = t2.bus_id where t1.type like ? order by t1.id, start_time desc";
        List<RegularBus> regularBusList = jdbc.query(
                sql,
                new Object[]{'%' + type + '%'},
                new BeanPropertyRowMapper(RegularBus.class)
        );
        return regularBusList;
    }

    @Override
    public List<RegularBus> getRegularBusById(int id) {
        String sql = "select start_time as geom from regular_bus_time where bus_id = ? order by start_time desc";
        List<RegularBus> regularBusList = jdbc.query(
                sql,
                new Object[]{id},
                new BeanPropertyRowMapper(RegularBus.class)
        );
        return regularBusList;
    }

    @Override
    public int insertRegularBus(RegularBus regularBus) {
        String syhq_to_bqfwq = "115.962351 40.439899 ,115.96146 40.439744 ,115.959121 40.436576 ,115.956332 40.436028 ,115.95115 40.435277 ,115.945839 40.434599 ,115.939541 40.433766 ,115.936591 40.432982 ,115.932997 40.431684 ,115.92628 40.488671";
        //世园海泉->板泉
        int insertNum = 0;
        regularBus = regularBus.toBuilder().
                origin(35).
                destination(19).
                geom("LINESTRING(" + syhq_to_bqfwq + ")").
                build();
        System.out.println(regularBus.toString());
        String sql = "INSERT into regular_bus_route(origin, destination, geom) values(?,?,ST_GEOMFROMTEXT(?))";
        insertNum += jdbc.update(sql, regularBus.getOrigin(), regularBus.getDestination(), regularBus.getGeom());
        return insertNum;
    }
}
